/**
 *  Licensed to the Apache Software Foundation (ASF) under one or more
 *  contributor license agreements.  See the NOTICE file distributed with
 *  this work for additional information regarding copyright ownership.
 *  The ASF licenses this file to You under the Apache License, Version 2.0
 *  (the "License"); you may not use this file except in compliance with
 *  the License.  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package com.tasktodorid.util;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

/**
 * TodoUtil 通用函数库
 *
 * @author qu sheng
 *
 */
public class TodoUtil {

    /**
     * 输出Log信息用的TAG
     */
    private static String TAG = TodoUtil.class.getName();

    /**
     * SQLite链接对象
     */
    public static SQLiteDatabase dbAccess = null;

    /**
     * 生成Task的Tid
     * @return Task的Tid
     */
    public static String generateTid() {
        StringBuilder sb = new StringBuilder();
        sb.append("T_");
        sb.append(UUID.randomUUID().toString().replaceAll("-", ""));
        return sb.toString();
    }

    /**
     * 生成Catalog的cid
     * @return Catalog的cid
     */
    public static String generateCid() {
        StringBuilder sb = new StringBuilder();
        sb.append("C_");
        sb.append(UUID.randomUUID().toString().replaceAll("-", ""));
        return sb.toString();
    }

    /**
     * 获取全部Task的数量，和未完成Task的数量
     *
     * @return List 全部信息的List
     */
    public static List getMainData() {
        List<Map> allCatalog = new ArrayList<Map>();

        Map<String, String> data = new HashMap<String, String>();

        // ALL Task
        data.put(ConstUtil.LIST_CATALOG_NAME, "全部");

        StringBuilder sb = new StringBuilder();

        sb.append("select count(1) as cnt ");
        sb.append("from m_task task left join sub_task sub ");
        sb.append("on (sub.sub_tid = task.tid ");
        sb.append("and sub.super_tid = '') ");

        Cursor cursor = dbAccess.rawQuery(sb.toString(), null);

        while (cursor.moveToNext()) {
            data.put(ConstUtil.LIST_CATALOG_TASK_NUM, cursor.getString(0) + "件");
        }

        allCatalog.add(data);

        sb.append("where task.completerate < 100");

        cursor = dbAccess.rawQuery(sb.toString(), null);

        data = new HashMap<String, String>();
        data.put(ConstUtil.LIST_CATALOG_NAME, "未完成");

        while (cursor.moveToNext()) {
            data.put(ConstUtil.LIST_CATALOG_TASK_NUM, cursor.getString(0) + "件");
        }

        allCatalog.add(data);
        cursor.close();
        return allCatalog;
    }

    /**
     * 获取全部Catalog所包含的Task的数量信息
     *
     * @return List 全部Catalog所包含的Task的数量
     */
    public static List getAllCatalog() {
        List<Map> allCatalog = new ArrayList<Map>();
        StringBuilder sb = new StringBuilder();

        sb.append("select a.cid cid, a.name name,  ifnull(b.cnt,0) cnt ");
        sb.append("from M_CATALOG a left join ");
        sb.append("(select cid, count(cid) cnt from catalog_task group by cid) b on a.cid = b.cid ");

        Cursor cursor = dbAccess.rawQuery(sb.toString(), null);

        while (cursor.moveToNext()) {
            Map<String, String> data = new HashMap<String, String>();
            // 名称列
            int colIndex = cursor.getColumnIndex("name");
            data.put(ConstUtil.LIST_CATALOG_NAME, cursor.getString(colIndex));
            // 主键
            colIndex = cursor.getColumnIndex("cid");
            // 件数
            data.put("cid", cursor.getString(colIndex));
            data.put(ConstUtil.LIST_CATALOG_TASK_NUM, cursor.getString(2).concat("件"));

            allCatalog.add(data);
        }

        return allCatalog;
    }

    /**
     * 初期化生成各类表
     */
    public static void initDataTable() {
        StringBuilder sb = new StringBuilder();

        // Create Table M_CATALOG
        sb.append("CREATE TABLE m_catalog ( ");
        sb.append("cid TEXT, ");
        sb.append("name TEXT, ");
        sb.append("PRIMARY KEY(cid ASC))");

        dbAccess.execSQL(sb.toString());

        // Create Table M_TASK
        sb = new StringBuilder();
        sb.append("create table m_task ( ");
        sb.append("tid text, ");
        sb.append("begindate text, ");
        sb.append("enddate text, ");
        sb.append("name text, ");
        sb.append("status number, ");
        sb.append("rank number, ");
        sb.append("completerate number, ");
        sb.append("primary key(tid asc)");
        sb.append(")");
        dbAccess.execSQL(sb.toString());

        // Create Table CATALOG_TASK
        sb = new StringBuilder();
        sb.append("CREATE TABLE catalog_task ( ");
        sb.append("cid text, ");
        sb.append("tid text, ");
        sb.append("primary key(cid, tid asc) ");
        sb.append(")");
        dbAccess.execSQL(sb.toString());

        // Create Table TaskTree
        sb = new StringBuilder();
        sb.append("CREATE TABLE sub_task (");
        sb.append("sub_tid text,  ");
        sb.append("super_tid text, ");
        sb.append("primary key( sub_tid asc) ");
        sb.append(")");
        dbAccess.execSQL(sb.toString());

        // 开发模式下
        if (ConstUtil.RUNMODE != ConstUtil.RUNMODE_REALASE) {
            insertDebugData();
        }
    }


    public static boolean addCatalog(String strCatalogName) {
        try {
            dbAccess.execSQL("insert into m_catalog values('" + generateCid() + "', '"+ strCatalogName + "')");
            return true;
        } catch (Exception ex) {
            return false;
        }
    }

    /**
     * 插入开发调试用数据
     */
    private static void insertDebugData() {
        String tid1 = generateTid();
        dbAccess.execSQL("insert into m_task values ('" + tid1 + "', '2011/03/14','','测试用task1',0,4,0)");
        String tid2 = generateTid();
        dbAccess.execSQL("insert into m_task values ('" + tid2 + "', '2011/03/14','','测试用task2',0,4,0)");
        String tid3 = generateTid();
        dbAccess.execSQL("insert into m_task values ('" + tid3 + "', '2011/03/14','','测试用task3',0,4,0)");
        String tid4 = generateTid();
        dbAccess.execSQL("insert into m_task values ('" + tid4 + "', '2011/03/14','','测试用task4',0,4,0)");
        String tid5 = generateTid();
        dbAccess.execSQL("insert into m_task values ('" + tid5 + "', '2011/03/14','','测试用task5',0,4,0)");


        String cid1 = generateCid();
        dbAccess.execSQL("insert into m_catalog values('" + cid1 + "', '测试目录1')");

        String cid2 = generateCid();
        dbAccess.execSQL("insert into m_catalog values('" + cid2 + "', '测试目录2')");

        dbAccess.execSQL("insert into catalog_task values('" + cid1 + "', '"+ tid1 + "')");
        dbAccess.execSQL("insert into catalog_task values('" + cid1 + "', '"+ tid2 + "')");

        dbAccess.execSQL("insert into catalog_task values('" + cid2 + "', '"+ tid3 + "')");
        dbAccess.execSQL("insert into catalog_task values('" + cid2 + "', '"+ tid4 + "')");
    }
}
